package dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import model.*;


public class ObjectDAO {

	ConnectionDB con;
	
	public ObjectDAO() {
		con = new ConnectionDB();
	};
	
	
	
	/**
	 * get a specific project from db
	 * @param projId
	 * @return Project
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public Project getAProject(int projId) throws SQLException {
		con = new ConnectionDB();
		Project proj = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Project WHERE projectId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, projId);
            rs = ps.executeQuery();

            if (rs.next()) {
            	proj = new Project(rs.getInt(1), rs.getString(2), rs.getString(3));
            }
			

			rs.close();
			ps.close();
			return proj;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return proj;
	}
	
	
	/**
	 * get all projects from db
	 * @return list of Projects
	 * @throws SQLException
	 */
	public ArrayList<Project> getListEmployees() throws SQLException {
		con = new ConnectionDB();
		ArrayList<Project> list = new ArrayList<Project>();
		Statement st;
		ResultSet rs;

		try {
			String query = "SELECT * FROM Project";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {
            	Project proj = new Project();
            	proj.setProjectId(rs.getInt(1));
            	proj.setProjectName(rs.getString(2));
            	proj.setDescription(rs.getString(3));
            	
            	list.add(proj);
            }
			

			rs.close();
			st.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}		
		return list;
	}
	
	
	/**
	 * get a specific category from an id
	 * @param caId
	 * @return Category 
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public Category getACategory(int caId) throws SQLException {
		con = new ConnectionDB();
		Category ca = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Category WHERE categoryId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, caId);
            rs = ps.executeQuery();

            if (rs.next()) {
            	ca = new Category(rs.getInt(1), rs.getString(2), rs.getString(3));
            }
			

			rs.close();
			ps.close();
			return ca;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return ca;
		
	}
	
	
	/**
	 * get all categories from db
	 * list of Category
	 * @return
	 * @throws SQLException
	 */
	public ArrayList<Category> getListCategories() throws SQLException {
		con = new ConnectionDB();
		ArrayList<Category> list = new ArrayList<Category>();
		Statement st;
		ResultSet rs;

		try {
			String query = "SELECT * FROM Category";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {
            	Category ca = new Category();
            	ca.setCategoryId(rs.getInt(1));
            	ca.setCategoryName(rs.getString(2));
            	ca.setDescription(rs.getString(3));
            	
            	list.add(ca);
            }
			

			rs.close();
			st.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}		
		return list;
	}
	
	
	/**
	 * get list of sections following category id
	 * @param cateId
	 * @return list of Section
	 * @throws SQLException 
	 */
	@SuppressWarnings("static-access")
	public ArrayList<Section> getSectionByCategory(int cateId) throws SQLException {
		con = new ConnectionDB();
		ArrayList<Section> list = new ArrayList<Section>();

		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Section WHERE categoryId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, cateId);
            rs = ps.executeQuery();

            while (rs.next()) {
            	Section section = new Section();
            	section.setSectionId(rs.getInt(1));
            	section.setSectionName(rs.getString(2));
            	section.setDescription(rs.getString(3));
            	Category ca = getACategory(rs.getInt(4));
            	section.setCate(ca);
            	
            	list.add(section);
            }

			rs.close();
			ps.close();
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return list;
	}
	
	
	/**
	 * get a specific section from an id
	 * @param sectionId
	 * @return Section 
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public Section getASection(int sectionId) throws SQLException {
		con = new ConnectionDB();
		Section section = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Section WHERE sectionId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, sectionId);
            rs = ps.executeQuery();

            if (rs.next()) {
            	section = new Section(rs.getInt(1), rs.getString(2), rs.getString(3), getACategory(rs.getInt(4)));
            }

			rs.close();
			ps.close();
			return section;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return section;
		
	}
	
	
	/**
	 * get all sections from db
	 * @return list of Section
	 * @throws SQLException
	 */
	public ArrayList<Section> getListSections() throws SQLException {
		con = new ConnectionDB();
		ArrayList<Section> list = new ArrayList<Section>();
		Statement st;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Section";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {
            	Section section = new Section();
            	section.setSectionId(rs.getInt(1));
            	section.setSectionName(rs.getString(2));
            	section.setDescription(rs.getString(3));
            	Category ca = getACategory(rs.getInt(4));
            	section.setCate(ca);
            	
            	list.add(section);
            }
			

			rs.close();
			st.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return list;
	}
	
	
	/**
	 * get a specific title from an id
	 * @param titleId
	 * @return Title 
	 * @throws SQLException
	 */
	@SuppressWarnings("static-access")
	public Title getATitle(int titleId) throws SQLException {
		con = new ConnectionDB();
		Title title = null;;
		PreparedStatement ps;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Title WHERE TitleId = ?";
			
            ps = con.getConnection().prepareStatement(query);
            ps.setInt(1, titleId);
            rs = ps.executeQuery();

            if (rs.next()) {
            	title = new Title(rs.getInt(1), rs.getString(2), rs.getString(3));
            }
			

			rs.close();
			ps.close();
			return title;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return title;
	}
	
	
	
	/**
	 * Get all titles from db
	 * @return list of titles
	 * @throws SQLException
	 */
	public ArrayList<Title> getListTitles() throws SQLException {
		con = new ConnectionDB();
		ArrayList<Title> list = new ArrayList<Title>();
		Statement st;
		ResultSet rs;
		
		try {
			String query = "SELECT * FROM Title";
			
            st = ConnectionDB.getConnection().createStatement();
            rs = st.executeQuery(query);
            while (rs.next()) {
            	Title title = new Title();
            	title.setTitleId(rs.getInt(1));
            	title.setTitleName(rs.getString(2));
            	title.setDescription(rs.getString(3));
            	
            	list.add(title);
            }
			

			rs.close();
			st.close();
			return list;	
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			ConnectionDB.closeConnection();
		}
		return list;
	}
	
}
